
package com.jf.cloud.service.sysmanager.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jf.cloud.service.sysmanager.common.base.BaseService;
import com.jf.cloud.service.sysmanager.common.base.GenericsUtils;
import com.jf.cloud.service.sysmanager.common.base.OPaging;
import com.jf.cloud.service.sysmanager.common.db.BaseDao;
import com.jf.cloud.service.sysmanager.common.db.Condition;
import com.jf.cloud.service.sysmanager.common.db.Query;
import com.jf.cloud.service.sysmanager.common.util.StringUtil;
import com.jf.cloud.service.sysmanager.dao.IPubgMatchSettlementService;
import com.jf.cloud.service.sysmanager.entity.PubgMatchSettlement;


/**
 * 员工服务实现
 * 
 * @author 绝峰代码机1.0 创建时间：2018-03-31
 */
@Service("pubgMatchSettlementService")
public class PubgMatchSettlementServiceImpl extends BaseService<PubgMatchSettlement, Long> implements IPubgMatchSettlementService {

	/**
	 * 日志
	 */
	private static Log log = LogFactory.getLog(PubgMatchSettlementServiceImpl.class);

	/**
	 * JPA公共DAO
	 */
	@Resource
	private BaseDao baseDao;

	private RowMapper<PubgMatchSettlement> rowMapper = new BeanPropertyRowMapper<PubgMatchSettlement>(GenericsUtils.getSuperClassGenricType(this.getClass()));

	/**
	 * 根据主键查询员工
	 * 
	 * @author 绝峰代码机1.0 创建时间：2018-03-31
	 */
	public PubgMatchSettlement findPubgMatchSettlement(Long rowId) throws Exception {
		try {
			return this.baseDao.findObject("", rowMapper, rowId);
		} catch (Exception e) {
			throw new Exception("根据主键查询员工出错！", e);
		}
	}

	/**
	 * 根据查询条件获取员工列表
	 * 
	 * @author 绝峰代码机1.0 创建时间：2018-03-31
	 */
	public List<PubgMatchSettlement> findPubgMatchSettlementList(Condition qc) throws Exception {
		try {
			return this.baseDao.findList("", rowMapper);
		} catch (Exception e) {
			throw new Exception("根据查询条件获取员工列表出错！", e);
		}
	}

	/**
	 * 新增/修改员工
	 * 
	 * @author 绝峰代码机1.0 创建时间：2018-03-31
	 */
	@Transactional
	public PubgMatchSettlement saveOrUpdatePubgMatchSettlement(PubgMatchSettlement pubgMatchSettlement) throws Exception {
		try {
			return this.baseDao.saveAll(pubgMatchSettlement);
		} catch (Exception e) {
			throw new Exception("新增/修改员工出错！", e);
		}
	}

	/**
	 * 获取战绩提交列表
	 *
	 * @author 高冬冬 创建时间：2018-04-11
	 */
	public List<Map<String, Object>> findMatchSettlementList(Query query, String nickName) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT ");
			sb.append(" pu.ROW_ID AS rowId, ");
			sb.append(" IFNULL(pu.NICKNAME, pu.USER_ACCOUNT) AS nickName, ");
			sb.append(" IFNULL(pu.GAME_ROLE, '') AS gameRole, ");
			sb.append(" IFNULL(pu.PHOTO, '') AS photo, ");
			sb.append(" pm.MATCH_TYPE_ID AS matchTypeId, ");
			sb.append(" IFNULL(pm.MATCH_TYPE, '') AS matchType, ");
			sb.append(" pum.MATCH_ID AS matchId, ");
			sb.append(" IFNULL(pms.MATCH_RESULT, '') AS matchResult ");
			sb.append(" FROM ");
			sb.append(" pubg_user pu JOIN pubg_user_match pum ");
			sb.append(" ON pu.ROW_ID = pum.USER_ID ");
			sb.append(" AND pum.USER_STATE = 20 ");
			sb.append(" AND pu.USER_ROLE = 10 ");
			sb.append(" JOIN pubg_match pm ");
			sb.append(" ON pum.MATCH_ID = pm.ROW_ID ");
			sb.append(" LEFT JOIN pubg_match_settlement pms ");
			sb.append(" ON pu.ROW_ID = pms.USER_ID ");
			sb.append(" AND pum.MATCH_ID = pms.MATCH_ID ");
			String querySql = query.getSQL(sb.toString());
			List<Object> params = query.getParamList();
			if (nickName != null && !"".equals(nickName.trim())) {
				querySql += " AND (pu.NICKNAME LIKE ? OR pu.USER_ACCOUNT LIKE ?) ";
				params.add("%" + nickName + "%");
				params.add("%" + nickName + "%");
			}
			dataList = this.baseDao.findMapList(querySql, params.toArray());
		} catch (Exception e) {
			log.error("获取战绩提交列表出错！", e);
		}
		return dataList;
	}
	
	/**
	 * 获取 确认战绩页面列表数据
	 * 
	 * @author 朱建文 创建时间：2018年4月11日 下午9:35:39
	 */
	public Map<String, Object> getMatchSettlementAuditList(Long matchId, Long clubId) throws Exception {
		Map<String, Object> dataMap = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		try {
			
			// 查询队员奖励总额、战队奖励总额、俱乐部利润、报名费
			sb.append(" SELECT ");
			sb.append("   SUM(IF(USER_ID IS NOT NULL && TEAM_ID IS NOT NULL, OBTAIN_AWARD, 0)) userAward, ");
			sb.append("   SUM(IF(USER_ID IS NULL && TEAM_ID IS NOT NULL, OBTAIN_AWARD, 0)) teamAward, ");
			sb.append("   SUM(IF(USER_ID IS NULL && TEAM_ID IS NULL, OBTAIN_AWARD, 0)) clubAward, ");
			sb.append("   IFNULL(APPLY_FEE, 0) applyFee ");
			sb.append(" FROM ");
			sb.append("   pubg_match_settlement ");
			sb.append(" WHERE ");
			sb.append("   MATCH_ID = ? AND CLUB_ID = ? ");
			dataMap = this.baseDao.findMap(sb.toString(), matchId, clubId);
			
			// 查询所有名次的奖励金额
			sb = new StringBuffer();
			sb.append(" SELECT "); 
			sb.append("   b.ROW_ID rowId, b.PHOTO photo, b.NICKNAME nickname, b.GAME_ROLE gameRole, "); 
			sb.append("   a.OBTAIN_RANK obtainRank, a.OBTAIN_AWARD obtainAward, a.TEAM_ID teamId "); 
			sb.append(" FROM "); 
			sb.append("   pubg_match_settlement a LEFT JOIN pubg_user b ON a.USER_ID = b.ROW_ID "); 
			sb.append(" WHERE "); 
			sb.append("   a.MATCH_ID = ? AND a.CLUB_ID = ? AND a.USER_ID IS NOT NULL "); 
			sb.append(" ORDER BY a.OBTAIN_RANK ");
			List<Map<String, Object>> dataList = this.baseDao.findMapList(sb.toString(), matchId, clubId);
			dataMap.put("data", dataList);
		} catch (Exception e) {
			throw new RuntimeException("获取 确认战绩页面列表数据 出错！", e);
		}
		return dataMap;
	}

	/**
	 * 查询已结束比赛的获奖用户的信息
	 *
	 * @author 高冬冬 创建时间：2018-04-13
	 */
	public List<Map<String, Object>> findFinishMatchSettlement(Query query) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT ");
			sb.append(" pu.ROW_ID AS userId, ");
			sb.append(" pu.USER_ACCOUNT AS userAccount, ");
			sb.append(" IFNULL(pu.NICKNAME, '') AS nickName, ");
			sb.append(" IFNULL(pu.GAME_ROLE, '') AS gameRole, ");
			sb.append(" pm.ROW_ID AS matchId, ");
			sb.append(" pm.MATCH_NAME AS matchName, ");
			sb.append(" pms.OBTAIN_RANK AS obtainRank, ");
			sb.append(" pms.OBTAIN_AWARD AS obtainAward ");
			sb.append(" FROM ");
			sb.append(" pubg_match pm JOIN pubg_match_settlement pms ");
			sb.append(" ON pm.ROW_ID = pms.MATCH_ID AND pm.MATCH_STATE = '70' ");
			sb.append(" JOIN pubg_user pu ");
			sb.append(" ON pms.USER_ID = pu.ROW_ID ");
			sb.append(" WHERE pms.RESULT_CONFIRM_ID IS NOT NULL AND pms.OBTAIN_RANK IS NOT NULL AND pms.OBTAIN_AWARD > 0 ");
			String querySql = query.getSQL(sb.toString());
			List<Object> params = query.getParamList();
			dataList = this.baseDao.findMapList(querySql, params.toArray());
		} catch (Exception e) {
			log.error("查询已结束比赛的获奖用户的信息出错！", e);
		}
		return dataList;
	}
	
	/**
	 * 队长、部长获取榜单列表
	 * 
	 * @author 朱建文 创建时间：2018年4月27日 上午10:28:39
	 */
	public List<Map<String, Object>> getMatchSettlement(OPaging<PubgMatchSettlement> page) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		try {
			StringBuffer sql = new StringBuffer();
			String whereSql = page.getWhereSQL(true);
			List<Object> paramList = page.getParamList();
			
			sql.append(" SELECT ");
			sql.append("   a.USER_ID userId, IFNULL(b.GAME_ROLE, '') gameRole, ");
			sql.append("   IFNULL(b.NICKNAME, '') nickName, IFNULL(b.PHOTO, '') photo, a.obtainNum ");
			sql.append(" FROM ");
			sql.append("   ( ");
			sql.append("     SELECT ");
			sql.append("       CLUB_ID, CLUB_NAME, TEAM_ID, TEAM_NAME, USER_ID, USER_ACCOUNT, 0 obtainAward, 0 obtainNum ");
			sql.append("     FROM ");
			sql.append("       pubg_user_team ");
			sql.append(whereSql);
			sql.append("   UNION ");
			sql.append("     SELECT ");
			sql.append("       CLUB_ID, CLUB_NAME, TEAM_ID, TEAM_NAME, USER_ID, USER_ACCOUNT, ");
			sql.append("       SUM(OBTAIN_AWARD) obtainAward, COUNT(OBTAIN_RANK) obtainNum  ");
			sql.append("     FROM ");
			sql.append("       pubg_match_settlement ");
			if (!StringUtil.isBlank(whereSql)) {
				sql.append(whereSql).append(" AND USER_ID IS NOT NULL ");
				paramList.addAll(page.getParamList());
			}
			sql.append("     GROUP BY USER_ID ");
			sql.append("     ORDER BY obtainNum DESC ");
			sql.append("   ) a LEFT JOIN pubg_user b ON a.USER_ID = b.ROW_ID ");
			sql.append(" GROUP BY a.USER_ID ");
			sql.append(" ORDER BY a.obtainNum DESC, a.obtainAward DESC, a.USER_ID ");
			sql.append(page.getLimitStr());
			
			dataList = this.baseDao.findMapList(sql.toString(), paramList.toArray());
		} catch (Exception e) {
			log.error("队长、部长获取榜单列表出错！", e);
		}
		return dataList;
	}
}
